using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Xml.Serialization;
using BLToolkit.Data;
using System.Linq;
using BLToolkit.Data.Linq;
using BLToolkit.DataAccess;
using dbpack;
using dbupd.data;

namespace dbupd.context
{
    #region | ROLES |

    // Database
    internal static class DbRole
    {
        internal static string GetCurrentVersion(this DbManager self)
        {
            return self.SetCommand(
                @"select top 1 name from db_curr_version
                order by id desc").ExecuteScalar<string>();
        }

        internal static void SetCurrentVersion(this DbManager self, string nver)
        {
            new SqlQuery<db_curr_version>().Insert(new db_curr_version() {name = nver});
        }
    }

    // Moment interval
    internal static class UpdaterRole
    {
        internal static void Log(this db_update self, int seq, char status, string message)
        {
            using (var xdb = new DbManager())
            {
                new SqlQuery<db_log>().Insert(xdb, new db_log() {id_update = self.id, sequence = seq, status = status, error = message});
            }
        }

        internal static db_update Start(this db_update self, Guid id_pack, string ver)
        {
            var s = new db_update() { on_date = self.on_date, version = ver, status = Update.Status_InProc, id_pack = id_pack };
            using (var db = new DbManager())
            {
                var id = db.InsertWithIdentity(s);
                s.id = Convert.ToInt32(id);
            }

            return s;
        }

        internal static void EndSuccess(this db_update self)
        {
            using (var db = new DbManager())
            {
                self.status = Update.Status_Success;
                db.Update(self);
            }
        }

        internal static void EndFailure(this db_update self, string message)
        {
            using (var db = new DbManager())
            {
                self.status = Update.Status_Failure;
                self.error = message;
                db.Update(self);
            }
        }
    }

    // Version apply through Sqlcommand transactions
    internal static class SqlCommandRole
    {
        internal static void AddVersionControlInfo(this SqlCommand self)
        {
            #region | VERSION CONTROL INFO |
            self.RunScript(
@"
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('db_curr_version') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
BEGIN

	CREATE TABLE db_curr_version ( 
		id bigint identity(1,1)  NOT NULL,
		name varchar(50)
	);

	ALTER TABLE db_curr_version ADD CONSTRAINT PK_db_curr_version 
		PRIMARY KEY CLUSTERED (id);

	ALTER TABLE db_curr_version
		ADD CONSTRAINT UQ_db_curr_version_id UNIQUE (id);

	INSERT INTO db_curr_version values('0');
END


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('db_update') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
BEGIN 

CREATE TABLE [dbo].[db_update](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[id_pack] [uniqueidentifier] NOT NULL,
	[on_date] [datetime] NOT NULL,
	[version] [varchar](20) NOT NULL,
	[status] [char](1) NOT NULL,
	[error] [text] NULL,
 CONSTRAINT [PK_db_update] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_db_update_id] UNIQUE NONCLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('db_log') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
BEGIN

CREATE TABLE [dbo].[db_log](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[id_update] [bigint] NOT NULL,
	[sequence] [int] NOT NULL,
	[status] [char](1) NOT NULL,
	[error] [text] NULL,
 CONSTRAINT [PK_db_log] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_db_log_id] UNIQUE NONCLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END");
            #endregion
        }

        internal static void ApplyVersion(this SqlCommand self, db_update update, ver ver, Action<string> setStatus)
        {
            var transaction = self.Connection.BeginTransaction();
            self.Transaction = transaction;
           
            try
            {
                foreach (var script in ver.scripts)
                {
                    try
                    {
                        var scr = script.body.Replace("&gt;", ">").Replace("&lt;", "<").Replace("&amp;", "&");
                        self.RunScript(scr);
                        update.Log(script.sequence, Update.Status_Success, null);
                        setStatus("Script " + script.name + " status: " + Update.Status_Success);
                    }
                    catch (Exception ex)
                    {
                        var message = ex.Message;
                        if (ex.InnerException != null) message = ex.InnerException.Message;

                        update.Log(script.sequence, Update.Status_Failure, message);
                        setStatus(message);
                        throw ex;
                    }
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
        }

        internal static void RunScript(this SqlCommand self, string script)
        {
            var regexp = new Regex(@"^(\s|\t)*go(\s\t)?.*", RegexOptions.Multiline | RegexOptions.IgnoreCase);

            var clearScript = regexp.Split(script).First();
            
            self.CommandText = clearScript;
            self.ExecuteNonQuery();
        }
    }

    // Update package
    internal static class PackRole
    {
        internal static List<ver> GetUpdateInfo(this pack self, List<string> vers)
        {
            return self.vers.Where(v => vers.Contains(v.name)).ToList();
        }
    }

    // Database Version
    internal static class VerRole
    {
        internal static string NextAfter(this List<string> project_versions, string current)
        {
            int ind = project_versions.IndexOf(current);
            if (ind >= project_versions.Count) 
                throw new UpdateException("Next version not presented in project");
            return project_versions[ind++];
        }

        internal static List<string> AllAfter(this List<string> versions, string current)
        {
            int ind = 0;
            
            if (current != "0") versions.IndexOf(current);
            if (current != "0" && ind >= versions.Count) 
                throw new UpdateException("Next version not presented in update package");

            versions.RemoveRange(0, ind);
            return versions;
        }

        internal static bool ContainsAllWithSequence(this List<string> project_versions, List<string> versions)
        {
            var shift = project_versions.IndexOf(versions[0]);

            for (int i = 0; i < versions.Count - 1; i++)
            {
                if (project_versions[shift + i] != versions[i]) return false;
            }

            return true;
        }

        internal static bool ContainVersion(this List<string> project_versions, string version)
        {
            if (version == "0") return true;
            return project_versions.Contains(version);
        }
    }

    #endregion

    public class Update
    {
        internal const char Status_Success = 'S';
        internal const char Status_Failure = 'F';
        internal const char Status_InProc  = 'I';
        
        private pack pack;
        private DbManager db;

        private List<string> project_versions;
        private List<string> update_versions;

        private string current_version;
        private string dbName;

        private db_update upd;

        private readonly Action<string> setStatus;

        public Update( string dbName_, string fileName, Action<string> status)
        {
            dbName = dbName_;
            var xml = new XmlSerializer(typeof(pack));
            using (var tr = new StreamReader(fileName)) {
                pack = (pack) xml.Deserialize(tr);
            }
            setStatus = status;
            upd = new db_update() { on_date = DateTime.Now };
        }

        public void Execute()
        {
            using (db = new DbManager(dbName))
            {
                // create connection
                var connection = (SqlConnection)db.Connection;
                
                // add version control data
                var command  = connection.CreateCommand();
                //command.Transaction = connection.BeginTransaction();
                command.AddVersionControlInfo();

                // get current db version
                current_version = db.GetCurrentVersion();

                // bind project version
                project_versions =
                    pack.project_versions.Split(new[] {", "}, StringSplitOptions.RemoveEmptyEntries).ToList();

                // validate operation
                if (!project_versions.ContainVersion(current_version)) 
                    throw new UpdateException("Version not found in project");

                // bind pack vesions
                update_versions = pack.pack_versions.Split(new[] {", "}, StringSplitOptions.RemoveEmptyEntries).ToList();
                // bind update versions
                update_versions = update_versions.AllAfter(current_version);

                // validate versions sequence
                if (!project_versions.ContainsAllWithSequence(update_versions))
                    throw new UpdateException("versions sequence not respected in update package");

                // update database
                var vd = pack.GetUpdateInfo(update_versions);

                // update version one by one
                try
                {
                    foreach (var ver in vd)
                    {
                        // update to version
                        upd = upd.Start(pack.id, ver.name);
                        command.ApplyVersion(upd, ver, setStatus);
                        // update info
                        db.SetCurrentVersion(ver.name);
                        upd.EndSuccess();
                    }
                }
                catch(Exception ex)
                {
                    // save failure
                    upd.EndFailure(ex.Message);
                    //Call delegate
                    setStatus(ex.Message);
                }
           }
        }
    }

    public class UpdateException: Exception
    {
        public UpdateException(string message) : base(message){}
    }
}